<?php
class Model_Agenda extends Model_WotHub {
    const AGENDA_CREATE_TEAM = 'creation_team';
    const AGENDA_CREATE_EVENT = 'creation_event';
    
    public function getEventType() {
    	$sql = 'SELECT *, 0 as esl_id FROM events 
                WHERE isEsl = 0
                UNION SELECT event_id, esl_name as nom, image, limite, heavy, medium, light, spg, td, isEsl, m.esl_id
    	        FROM events ev
                JOIN esl_team e ON e.esl_type = ev.event_id AND ev.isEsl = 1
                JOIN esl_membres m ON m.account_id = :accountid AND m.esl_id = e.esl_id
                WHERE ev.isEsl = 1
                ORDER BY event_id';
    	$listeEvent = $this->executerSql($sql, array(':accountid' => $_SESSION['account_id']));
//     	foreach ($listeEvent as $event) {
//     	    $events[$event['event_id']] = $event;
//     	}
    	return $listeEvent;
    }
    
    public function insererEvent() {
        $sql = 'INSERT INTO agenda VALUES(null, :date, :clanid, :eventid, :eslteam)';
        $bind = array(':date' => $_POST['dateEvent'],
                      ':clanid' =>$_SESSION['clanInfo']['clan_id'],
                      ':eventid' => substr($_POST['typeEvent'],0, strpos($_POST['typeEvent'], '_')),
                      ':eslteam' => substr($_POST['typeEvent'], strpos($_POST['typeEvent'], '_')+1),
        );
        $insert = $this->executerSql($sql, $bind, 'agenda_id');
        SmartbokMessage::addMessage(SmartbokMessage::INFO, 'l\'événement a été ajouté.');
        return $insert;
    }
    
    public function getEvent($date= null, $limite= null) {
        $Events = array();
        $bind = array(':clanid' => $_SESSION['clanInfo']['clan_id'],
                      ':accountid' => $_SESSION['account_id']
        );
        $limit = '';
        $selectDate = '';
        if ($date !== null) {
            $selectDate = " AND a.date >= :date";
            $bind[':date'] = $date;
        }
        
        if($limite !== null) {
            $limit = "LIMIT 0, $limite";
            //$bind[':limite'] = $limite;
        }
        
        $sql = "SELECT * FROM (SELECT a.agenda_id, a.date, e.nom, e.image, d.titre, d.visibilite, a.clan_id, null esl_id
              FROM agenda a 
              JOIN events e ON e.event_id = a.event_id
              LEFT JOIN agenda_detail d ON d.agenda_id = a.agenda_id
              LEFT JOIN agenda_clan c ON c.agenda_id = a.agenda_id AND c.clan_id = :clanid
              WHERE (a.clan_id = :clanid OR d.visibilite = 2 OR d.visibilite = 3 )
              AND a.esl_team = 0
              $selectDate
                UNION SELECT a.agenda_id, a.date,  esl.esl_name as nom, e.image, null titre, null visibilite, a.clan_id, esl.esl_id
              FROM agenda a 
              JOIN events e ON e.event_id = a.event_id
              JOIN esl_team esl ON esl.esl_type = a.event_id AND esl.esl_id = a.esl_team
              JOIN esl_membres eslm ON esl.esl_id = eslm.esl_id
              WHERE eslm.account_id = :accountid
                $selectDate) AS t 
                ORDER BY date ASC
                $limit";

        $listeEvent = $this->executerSql($sql, $bind);

        foreach ($listeEvent as &$event) {
            $event[1] = 0;
            $event[2] = 0;
            $event[3] = 0;
            $inscriptions = $this->getInscriptions($event['agenda_id']);
            if ($event['titre'] !== null) {
                $event['nom'] = $event['titre'];
            }
            foreach($inscriptions as $inscription) {
                $event[$inscription['status']]+=1; 
            }
            
            $Events[$event['date']][$event['agenda_id']] = $event;
        }
        return $Events;
    }
    
    private function _getDetail($agendaId) {
        $sql = 'SELECT * FROM agenda_detail WHERE agenda_id = :agendaid';
        $detail = $this->executerSql($sql, array(':agendaid' => $agendaId));
        if (count($detail) === 1) {
            return $detail[0];
        }
        return false;
    }
    
    public function getLastEvent() {
        $date = strftime('%Y%m%d', time());
        $events = $this->getEvent($date, $_SESSION['clanInfo']['params']['nombre_event']);
        return $events;
    }
    
    public function getAgendaAction($baseUri, $creation) {
        $actions = array();
        $sql = 'SELECT * FROM agenda_action ORDER BY action_id';
        $listeActions = $this->executerSql($sql);
        foreach ($listeActions as $action) {
            if ($action['action_id'] !== '5' || ($action['action_id'] === '5' && $creation === true)) {
                $action['url'] = str_replace('#ACTIONID#', $action['action_id'], $action['url']);
                if($action['action_id'] === '4') {
                    $action['url'] = $baseUri.$action['url'];
                }
                $actions[$action['action_id']] = $action;
            }
        }
        return $actions;
    }
    
    public function deleteEvent($event_id) {
        $sql = 'DELETE FROM agenda WHERE agenda_id=:eventid';
        $bind = array(':eventid' => $event_id);
        $retour = $this->executerSql($sql, $bind);
        return $retour;        
    }
    
    private function _checkInscription($event_id) {
        $sql = 'SELECT status FROM inscription WHERE agenda_id=:agendaid AND account_id=:accountid';
        $bind = array(':agendaid' => $event_id,
                      ':accountid' => $_SESSION['account_id']);
        $result = $this->executerSql($sql, $bind);
        if (count($result) > 0) {
            return $result[0]['status'];
        }
        return false;
    }
    
    public function participerEvent($action_id, $event_id) {
        $sql = 'INSERT INTO inscription VALUES(:accountid, :agendaid, :status)';
        if ($this->_checkInscription($event_id) !== false) {
            $sql = 'UPDATE inscription SET status = :status WHERE agenda_id=:agendaid AND account_id=:accountid';
        }
        
        $bind = array(':agendaid' => $event_id,
                      ':accountid' => $_SESSION['account_id'],
                      ':status' => $action_id
        );
        $result = $this->executerSql($sql, $bind);
        return $result;
    }
    
    public function getMyInscription() {
        $listeInscription = array();
        $sql = 'SELECT * FROM inscription WHERE account_id=:accountid';
        $bind = array(':accountid' => $_SESSION['account_id']);
        $result = $this->executerSql($sql, $bind);
        foreach ($result as $inscription){
            $listeInscription[$inscription['agenda_id']] = $inscription['status'];
        }
        return $listeInscription;
    }
    
    public function getInscriptions($agendaId) {
        $sql = 'SELECT * FROM inscription WHERE agenda_id=:agendaId';
        $bind = array(':agendaId' => $agendaId);
        $listeInscription = $this->executerSql($sql, $bind);        
        return $listeInscription;
    }
    
    public function getDataInscrits($agendaId, $status= 1) {
        $inscriptions = array();
        $bind =  array(':agendaid' => $agendaId);
        
        $sql = 'SELECT IFNULL(d.heavy, e.heavy) heavy, IFNULL(d.medium, e.medium) medium, IFNULL(d.light,e.light) light, IFNULL(d.spg, e.spg) spg, IFNULL(d.td, e.td) td
                FROM events e
                JOIN agenda a ON a.event_id = e.event_id
                LEFT JOIN agenda_detail d ON d.agenda_id = a.agenda_id
                WHERE a.agenda_id = :agendaid';
        $tankLevel = $this->executerSql($sql, $bind);
        foreach ($tankLevel[0] as $type => $level) {
            $bind[":$type"] = $level;    
        }
        $bind[':status'] = $status;
        $sql = 'SELECT i.account_id, t.tank_id, t.name, t.image, t.level, t.nation, t.type, e.limite, e.heavy, e.medium, e.light, e.td, e.spg, e.event_id, i.status
                FROM inscription i
                JOIN agenda a ON a.agenda_id = i.agenda_id
                JOIN events e ON e.event_id = a.event_id
                JOIN garage g ON g.account_id = i.account_id AND g.actif = 1
                JOIN tanks t ON t.tank_id = g.tank_id AND (
                                                            (t.level = :heavy AND t.type =  "heavyTank")
                                                            OR 
                                                            (t.level = :medium AND t.type =  "mediumTank")
                                                            OR 
                                                            (t.level = :light AND t.type =  "lightTank")
                                                            OR 
                                                            (t.level = :td AND t.type =  "AT-SPG")
                                                            OR 
                                                            (t.level = :spg AND t.type =  "SPG")
                                                        )
                WHERE a.agenda_id = :agendaid AND i.status = :status
                
                UNION SELECT i.account_id, t.tank_id, t.name, t.image, t.level, t.nation, t.type, e.limite, e.heavy, e.medium, e.light, e.td, e.spg, e.event_id, i.status
                FROM inscription i
                JOIN agenda a ON a.agenda_id = i.agenda_id
                JOIN events e ON e.event_id = a.event_id
                JOIN garage g ON g.account_id = i.account_id AND g.actif = 1
                JOIN tanks t ON t.tank_id = g.tank_id AND t.level=1
                WHERE a.agenda_id = :agendaid AND i.status = :status
				AND e.event_id in (6,9)';

        $listeInscrits = $this->executerSql($sql, $bind);
        foreach ($listeInscrits as $inscrit) {
            $inscriptions[$inscrit['account_id']][] = $inscrit; 
        }
        return $inscriptions;
    }
    
    public function getDetailAgenda($agendaId) {
        $sql = 'SELECT a.agenda_id, a.date, a.clan_id, e.event_id, e.nom, image, e.limite, e.heavy, e.medium, e.light, e.spg, e.td,
                d.titre, d.visibilite, d.limite limited, d.heavy heavyd, d.medium mediumd, d.light lightd, d.td tdd, d.spg spgd
                FROM agenda a
                JOIN events e ON a.event_id = e.event_id
                LEFT JOIN agenda_detail d ON d.agenda_id = a.agenda_id
                WHERE a.agenda_id = :agendaid';
        $bind = array(':agendaid' => $agendaId);
        $result = $this->executerSql($sql, $bind);
        if ($result[0]['visibilite'] !== null) {
            $result[0]['nom'] = $result[0]['titre'];
            $result[0]['limite'] = $result[0]['limited'];
            $result[0]['heavy'] = $result[0]['heavyd'];
            $result[0]['medium'] = $result[0]['mediumd'];
            $result[0]['light'] = $result[0]['lightd'];
            $result[0]['spg'] = $result[0]['spgd'];
            $result[0]['td'] = $result[0]['tdd'];
        }
        return $result[0];
    }
    
    public function enregistrerEquipe($agenda_id) {
         if (isset($_POST['TeamMembers']) === true) {
             $this->deleteTeam($agenda_id);
             if ($_POST['TeamMembers'] !== '') {
                 $teamMembers = explode('#', $_POST['TeamMembers']);
                 foreach ($teamMembers as $member) {
                     $data = explode('|', $member);
                     $bind = array(':account_id' => $data[0],
                                   ':tank_id' => $data[1],
                                   ':agenda_id' => $agenda_id                     
                     );
                     $sql = 'INSERT INTO agenda_team  VALUES(:agenda_id, :account_id, :tank_id)';
                     $this->executerSql($sql, $bind);
                 }
             }
         }
    }
    
    public function deleteTeam($agenda_id) {
        $sql = 'DELETE FROM agenda_team WHERE agenda_id = :agenda_id';
        $this->executerSql($sql, array(':agenda_id' => $agenda_id));
    }
    
    public function getTeam($agenda_id) {
        $team = array();
        $sql = 'SELECT e.account_id, e.tank_id, t.name, t.image, t.level, t.nation
                FROM agenda_team e
                JOIN tanks t on t.tank_id = e.tank_id
                WHERE e.agenda_id = :agenda_id';
        $Selectedmembers = $this->executerSql($sql, array(':agenda_id' => $agenda_id));
        
        foreach ($Selectedmembers as &$member) {
            $team[$member['account_id']] = $member; 
        }
        return $team;
    }
    
    public static function getListeEventType() {
        global $application;
        $pdo = new smartbokModele($application['bdd']);
        $lstEvents = $pdo->executerSql('SELECT * FROM events');
        return $lstEvents;
    }
    
    public static function getShortEventListe($clanId) {
        global $application;
        $pdo = new smartbokModele($application['bdd']);
        $sql = 'SELECT * FROM clan WHERE clan_id = :cid';
        $bind = array(':cid' => $clanId);
        $clan = $pdo->executerSql($sql, $bind);
        $startDate = date('Ymd');
        if (count($clan) > 0) {
            $limite = $clan[0]['nombre_event'];
            $bind[':date'] = $startDate;
            $sql = "SELECT a.agenda_id, a.date, e.nom, e.image, d.titre, d.visibilite, a.clan_id, null esl_id
            FROM agenda a
            JOIN events e ON e.event_id = a.event_id
            LEFT JOIN agenda_detail d ON d.agenda_id = a.agenda_id
            LEFT JOIN agenda_clan c ON c.agenda_id = a.agenda_id AND c.clan_id = :cid
            WHERE (a.clan_id = :cid OR d.visibilite = 2 OR d.visibilite = 3 )
            AND a.esl_team = 0
            AND a.date >= :date
            ORDER BY a.date
            LIMIT 0, $limite";            
            $events = $pdo->executerSql($sql, $bind);
            if (count($events) > 0) {
                foreach ($events as $event) {
                    $tmpEvent[$event['date']][] = $event;
                }
                return $tmpEvent;
            }
            else {
                return false;
            }
        }
        else {
            return false; 
        }
    }
}